<%@ include file="includes/header.jsp"%>
<%@ page import="java.util.*"%>
<%@ page import="java.lang.Object"%>

<%
	// connect to the database
	Class.forName("com.mysql.jdbc.Driver");
	Connection con = DriverManager
			.getConnection("jdbc:mysql://127.0.0.1/kroketweb",
					"kroketweb", "password");
	Statement st = con.createStatement();

	// get the pagination vars
	String pageNumberStr = request.getParameter("pag");
	int pageNumber = 1;
	if (pageNumberStr != null) {
		pageNumber = Integer.parseInt(pageNumberStr);
		if (pageNumber <= 0) {
			pageNumber = 1;
		}
	}
	String prodPerPageStr = request.getParameter("ppp");
	int prodPerPage = 15;
	if (prodPerPageStr != null) {
		prodPerPage = Integer.parseInt(prodPerPageStr);
		if (prodPerPage <= 4) {
			prodPerPage = 5;
		}
	}
	int startProd = (pageNumber - 1) * prodPerPage;
	String limitStr = " LIMIT " + startProd + ", " + prodPerPage;

	// get the number of products and calculate the number of pages
	ResultSet numProd = st
			.executeQuery("SELECT COUNT(product_id) AS product_count FROM product;");
	numProd.next();
	int numProdInt = numProd.getInt("product_count");
	int pageAmount = (int)Math.ceil((double)numProdInt/(double)prodPerPage);

	// Generate MySQL compatible current date
	java.text.DateFormat dateFormat = new java.text.SimpleDateFormat(
			"yyyy-MM-dd");
	java.util.Date date = new java.util.Date();
	String sDate = dateFormat.format(date);

	// Build query
	Statement productStatement = con.createStatement();
	ResultSet rs = productStatement
			.executeQuery("SELECT p.product_id AS product_id, p.product_name AS product_name, p.product_stock AS product_stock, q.price_value AS price_value"
					+ " FROM product p"
					+ " INNER JOIN price q"
					+ " ON p.product_id = q.product_id"
					+ " WHERE q.price_sDate < '"
					+ sDate
					+ "'"
					+ " AND (q.price_eDate > '"
					+ sDate
					+ "' OR q.price_eDate IS NULL)" + limitStr + ";");
%>

<h1>Overzicht producten</h1>

<table id="products">
	<thead>
		<tr>
			<th>Product</th>
			<th>Prijs(Euro)</th>
			<th>Op voorraad</th>
			<th></th>
		</tr>
	</thead>

	<tbody>
		<%
			int i = 1;
			while (rs.next()) {
				int product_id = rs.getInt("product_id");
				String name = rs.getString("product_name");
				int stock = rs.getInt("product_stock");
				Float fPrice = rs.getFloat("price_value");

				String sPrice = fPrice.toString();
				String price = sPrice.replaceAll("\\.", "\\,");

				String values[] = price.split(",");
				if (values[1].length() == 1) {
					values[1] += "0";
				}

				price = values[0] + "," + values[1];
		%>
		<tr>
			<%
				if (stock > 0) {
						String className = "";
						boolean evenRow = false;

						if (i % 2 == 0) {
							className = "class=\"evenRow\"";
							evenRow = true;
						}
						out.println("<td " + className
								+ " onclick=\"toggleOrderAmountField(" + product_id
								+ ", " + evenRow
								+ ");\" style=\"cursor: pointer;\" id=\"product"
								+ product_id + "\">" + name + "</td>");
						out.println("<td " + className
								+ " onclick=\"toggleOrderAmountField(" + product_id
								+ ", " + evenRow
								+ ");\" style=\"cursor: pointer;\" id=\"price"
								+ product_id + "\">&euro; " + price + "</td>");
						out
								.println("<td class=\"available\" onclick=\"toggleOrderAmountField("
										+ product_id
										+ ", "
										+ evenRow
										+ ");\" style=\"cursor: pointer;\">Ja</td>");
					} else {
						String className = "";
						if (i % 2 == 0) {
							className = "class=\"evenRow\"";
						}
						out.println("<td " + className + ">" + name + "</td>");
						out.println("<td " + className + ">&euro; " + price
								+ "</td>");
						out.println("<td class=\"unavailable\">Nee</td>");
					}
			%>
			<td id="amountField<%=product_id%>"></td>
		</tr>
		<%
			++i;
			}
		%>
		<tr>
			<td>Producten per pagina</td>
			<td><select onchange="setProductsPerPage(<%= pageNumber %>);" id="pppSelector">
				<option value="5"<% if (prodPerPage == 5) { out.print(" selected=\"selected\""); } %>>5</option>
				<option value="10"<% if (prodPerPage == 10) { out.print(" selected=\"selected\""); } %>>10</option>
				<option value="15"<% if (prodPerPage == 15) { out.print(" selected=\"selected\""); } %>>15</option>
			</select></td>
			<td>
			<%
			if (pageNumber > 1) {
				out.println("<a href=\"products.jsp?pag=" + (pageNumber - 1) + "&ppp=" + prodPerPage + "\" class=\"pagination\">&laquo;</a>");
			}
			for (int j = 1; j <= pageAmount; ++j) {
				out.println("<a href=\"products.jsp?pag=" + j + "&ppp=" + prodPerPage + "\" class=\"pagination\">" + j + "</a>");
			}
			if (pageNumber < pageAmount) {
				out.println("<a href=\"products.jsp?pag=" + (pageNumber + 1) + "&ppp=" + prodPerPage + "\" class=\"pagination\">&raquo;</a>");
			}
			%>
			</td>
		</tr>
	</tbody>
</table>
<%@ include file="includes/footer.jsp"%>